package Technical;

import Domain.BlogBean;
import Domain.CommentBean;
import Domain.PostBean;
import Domain.UserBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Database {

	private String user = "dbtht1118", password = "nafseq09";
	
	public Connection connect() throws ClassNotFoundException, SQLException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		Connection connection = null;
		connection = DriverManager.getConnection("jdbc:sqlserver://idasql-db.hb.se:56001; user=" + user + ";password=" + password);

		return connection;
	}
	
	public void addNewUser(UserBean user) {
		String sql = "INSERT INTO Account VALUES ('" + user.getuserName() + "', '" + user.getpassword() + "', '" + user.getfirstName() + "', '" + user.getlastName() + "')";
		Connection connection = null;
		try {
			connection = connect();
			if (connection != null) {
				Statement statement = connection.createStatement();
				statement.executeUpdate(sql);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
	}
	
	public void addNewBlogg(UserBean user, String blogName) {
		String sql = "INSERT INTO Blog VALUES (" + user.getuserId() + ", '" + blogName + "')";
		Connection connection = null;
		try {
			connection = connect();
			if (connection != null) {
				Statement statement = connection.createStatement();
				statement.executeUpdate(sql);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
	}
	
	public void addNewPost(int blogId, PostBean post) {
		String sql = "INSERT INTO Post VALUES (" + blogId + ", '" + post.getHeader() + "', '" + post.getText() + "', GETDATE())";
		Connection connection = null;
		try {
			connection = connect();
			if (connection != null) {
				Statement statement = connection.createStatement();
				statement.executeUpdate(sql);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
	}
	
	public void addNewComment(PostBean post, CommentBean comment) {
		String sql = "INSERT INTO Comment VALUES (" + post.getpostId() + ", " + comment.getuser().getuserId() + ", '" + comment.gettext() + "', GETDATE())";
		Connection connection = null;
		try {
			connection = connect();
			if (connection != null) {
				Statement statement = connection.createStatement();
				statement.executeUpdate(sql);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
	}
	
	public boolean userExists(String userName){
		String sql = "SELECT count(*) as count FROM Account WHERE username = '" + userName + "'";
		Connection connection = null;
		ResultSet resultSet = null;
		int count = 0;
			try {
				connection = connect();
				if(connection != null){
					Statement statement = connection.createStatement();
					resultSet = statement.executeQuery(sql);
					resultSet.next();
					count = resultSet.getInt("count");
				}
			} catch (ClassNotFoundException ex) {
				Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
			} catch (SQLException ex) {
				Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
			}
		return count == 1;
	}
	
	public boolean validatePassword(String userName, String password){
		String sql = "SELECT pass FROM Account WHERE username = '" + userName + "'";
		Connection connection = null;
		ResultSet resultSet = null;
		String tmppass = null;
        
        if(!userExists(userName))
            return false;
        
		try {
			connection = connect();
			if(connection!=null){
				Statement statement = connection.createStatement();
				resultSet = statement.executeQuery(sql);
				resultSet.next();
				tmppass = resultSet.getString("pass");
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		
		if(tmppass.equals(password) && tmppass != null)
			return true;
		else
			return false;
	}
	
	public BlogBean getBlog(int blogId){
		BlogBean tmpblog = new BlogBean();
		
		String sql = "SELECT * FROM Blog WHERE blogId =" + blogId;
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			resultSet.next();
			tmpblog.setblogName(resultSet.getString("BlogName"));
			tmpblog.setblogId(resultSet.getInt("BlogId"));
			tmpblog.setuser(getUser(resultSet.getInt("userId")));
			
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		tmpblog.setPostsList(getPosts(tmpblog.getblogId(),tmpblog.getuser()));
		
		return tmpblog;
	}
	
	public ArrayList<PostBean> getPosts(int blogId, UserBean user){
		ArrayList<PostBean> postlist = new ArrayList();
		
		String sql = "SELECT * FROM Post WHERE BlogId = " + blogId;
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()){
				PostBean tmp = new PostBean();
				tmp.setAuthor(getUser(user.getuserId()));
				tmp.setDate(resultSet.getDate("postTime"));
				tmp.setHeader(resultSet.getString("header"));
				tmp.setText(resultSet.getString("postText"));
				tmp.setpostId(resultSet.getInt("postId"));
				tmp.setCommentList(getComments(resultSet.getInt("postId")));
				postlist.add(tmp);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		
		
		return postlist;
	}
	
	public ArrayList<CommentBean> getComments(int postId){
		ArrayList<CommentBean> commentlist = new ArrayList();
		
		String sql = "SELECT * FROM Comment WHERE postId = " + postId;
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()){
				CommentBean tmp = new CommentBean();
				tmp.setdate(resultSet.getDate("commentTime"));
				tmp.settext(resultSet.getString("commentText"));
				tmp.setuser(getUser(resultSet.getInt("userId")));
				tmp.setcommentId(resultSet.getInt("kommentarId"));
				commentlist.add(tmp);
			}
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		return commentlist;
	}

	public UserBean getUser(int userId) {
		UserBean user = new UserBean();
		
		String sql = "SELECT * FROM Account WHERE userId = " + userId;
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			resultSet.next();
			
			user.setuserId(resultSet.getInt("userId"));
			user.setuserName(resultSet.getString("username"));
			user.setpassword(resultSet.getString("pass"));
			user.setfirstName(resultSet.getString("firstname"));
			user.setlastName(resultSet.getString("surname"));
			
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		
		return user;
	}
	
	public int getUserId(String username){
		String sql = "SELECT userId FROM Account WHERE username = " + "'"+username+"'";
		
		int userId = 0;
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
            resultSet.next();
			userId = resultSet.getInt("userId");
			
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		return userId;
	}
	
	public int countComments(PostBean post){
		String sql = "SELECT count(*) as count FROM Comment WHERE postId =" + post.getpostId();
		Connection connection = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			resultSet.next();
			count = resultSet.getInt("count");
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		return count;
	}
	
	public int getBlogByUserId(int userId){
		String sql = "SELECT * FROM Blog WHERE UserId = " + userId;
		Connection connection = null;
		ResultSet resultSet = null;
		int blogId = 0;
		try {
			connection = connect();
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			resultSet.next();
			blogId = resultSet.getInt("BlogId");
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
		}
		return blogId;
	}
}
